package com.itheima.mapper;

import com.github.pagehelper.Page;
import com.itheima.pojo.Student;
import com.itheima.pojo.StudentCountData;
import com.itheima.pojo.StudentQueryParam;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface StudentMapper {
    /**
     * 条件查询员工列表
     * @param studentQueryParam
     * @return
     */
    Page<Student> list(StudentQueryParam studentQueryParam);

    /**
     * 新增员工
     * @param student
     */
    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into student(name,no,gender,phone,degree,clazz_id,id_card,is_college,address,graduation_date) " +
            "values(#{name},#{no},#{gender},#{phone},#{degree},#{clazzId},#{idCard},#{isCollege},#{address},#{graduationDate})")
    void insert(Student student);

    /**
     * 根据id查询员工
     * @param id
     */
    @Select("select * from student where id = #{id}")
    Student get(Integer id);


    /**
     * 修改员工
     * @param student
     */
    @Update("update student set name = #{name},no = #{no},gender = #{gender},phone = #{phone},degree = #{degree},clazz_id = #{clazzId},id_card = #{idCard},is_college = #{isCollege},address = #{address},graduation_date = #{graduationDate} where id = #{id}")
    void update(Student student);

    /**
     * 批量删除员工
     * @param ids
     */
    void deleteByIds(List<Integer> ids);

    /**
     * 违纪扣分
     * @param id
     * @param score
     */

    @Update("update student set violation_count = violation_count + 1,violation_score = violation_score + #{score} where id = #{id}")
    void violation(Integer id, Short score);


    /**
     * 获取学历数据
     * @return
     */
    @Select("""
   select
        case degree when 1 then '初中'
                when 2 then '高中'
                when 3 then '大专'
                when 4 then '本科'
                when 5 then '硕士'
                when 6 then '博士'
                else '其他学历'
        end as name,
        count(*) as value
        from student group by degree 
    """)
    List<Map>  countStudentDegreeData();

    /**
     * 获取员工人数统计数据
     * @return
     */
    @Select("""
    
            select
               case clazz_id when 1 then 'JavaEE就业163期'
                    when 2 then   '前端就业90期'
                    when 3 then  'JavaEE就业165期 '
                    when 4 then 'JavaEE就业166期'
                    when 5 then '大数据就业58期'
                    when 6 then 'JavaEE就业167期'
                    else '其他班级'
            end as clazz_name,
            count(clazz_id) as num
            from student group by clazz_id
            order by num
    """)
    List<StudentCountData> studentStudentData();

}
